const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || '14.103.192.86',
  port: process.env.DB_PORT || 3308,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Maichong123',
  database: process.env.DB_NAME || 'hunaction',
  charset: 'utf8mb4',
  timezone: '+08:00',
  acquireTimeout: 60000,
  timeout: 60000,
  reconnect: true
};

// 创建连接池
const pool = mysql.createPool({
  ...dbConfig,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  multipleStatements: true
});

// 测试数据库连接
async function testConnection() {
  try {
    const connection = await pool.getConnection();
    console.log('✓ 数据库连接成功');
    connection.release();
    return true;
  } catch (error) {
    console.error('✗ 数据库连接失败:', error.message);
    return false;
  }
}

// 执行查询的通用方法
async function query(sql, params = []) {
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (error) {
    console.error('数据库查询错误:', error);
    throw error;
  }
}

// 执行事务
async function transaction(callback) {
  const connection = await pool.getConnection();
  await connection.beginTransaction();

  try {
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

// 获取单条记录
async function findOne(table, conditions = {}, fields = '*') {
  const whereClause = Object.keys(conditions).length > 0
    ? 'WHERE ' + Object.keys(conditions).map(key => `${key} = ?`).join(' AND ')
    : '';

  const sql = `SELECT ${fields} FROM ${table} ${whereClause} LIMIT 1`;
  const values = Object.values(conditions);

  const rows = await query(sql, values);
  return rows.length > 0 ? rows[0] : null;
}

// 获取多条记录
async function findMany(table, conditions = {}, options = {}) {
  const {
    fields = '*',
    orderBy = '',
    limit = '',
    offset = 0
  } = options;

  const whereClause = Object.keys(conditions).length > 0
    ? 'WHERE ' + Object.keys(conditions).map(key => `${key} = ?`).join(' AND ')
    : '';

  const orderClause = orderBy ? `ORDER BY ${orderBy}` : '';
  const limitClause = limit ? `LIMIT ${limit} OFFSET ${offset}` : '';

  const sql = `SELECT ${fields} FROM ${table} ${whereClause} ${orderClause} ${limitClause}`;
  const values = Object.values(conditions);

  return await query(sql, values);
}

// 插入记录
async function insert(table, data) {
  const fields = Object.keys(data).join(', ');
  const placeholders = Object.keys(data).map(() => '?').join(', ');
  const values = Object.values(data);

  const sql = `INSERT INTO ${table} (${fields}) VALUES (${placeholders})`;
  const result = await query(sql, values);

  return {
    insertId: result.insertId,
    affectedRows: result.affectedRows
  };
}

// 更新记录
async function update(table, data, conditions) {
  const setClause = Object.keys(data).map(key => `${key} = ?`).join(', ');
  const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');

  const sql = `UPDATE ${table} SET ${setClause} WHERE ${whereClause}`;
  const values = [...Object.values(data), ...Object.values(conditions)];

  const result = await query(sql, values);
  return result.affectedRows;
}

// 删除记录
async function remove(table, conditions) {
  const whereClause = Object.keys(conditions).map(key => `${key} = ?`).join(' AND ');
  const sql = `DELETE FROM ${table} WHERE ${whereClause}`;
  const values = Object.values(conditions);

  const result = await query(sql, values);
  return result.affectedRows;
}

// 获取记录总数
async function count(table, conditions = {}) {
  const whereClause = Object.keys(conditions).length > 0
    ? 'WHERE ' + Object.keys(conditions).map(key => `${key} = ?`).join(' AND ')
    : '';

  const sql = `SELECT COUNT(*) as total FROM ${table} ${whereClause}`;
  const values = Object.values(conditions);

  const rows = await query(sql, values);
  return rows[0].total;
}

module.exports = {
  pool,
  query,
  transaction,
  findOne,
  findMany,
  insert,
  update,
  remove,
  count,
  testConnection
};
